#include "statistiques.h"

Statistiques::Statistiques(QObject *parent, QSqlDatabase database) :
    QObject(parent)
{
    db = database;
}

QStringList Statistiques::consoMax(int id){
    return consoMax("",id);
}


QStringList Statistiques::consoMax(QString recherche, int id){

    QSqlDatabase db = QSqlDatabase::database();
    db.transaction();
    QSqlQuery q;

    if(id > 0){
        //Si le pg existe, on récupère la liste des consos du Pg, groupés par Intitulés, triées par nombre decroissant.
        q.prepare("SELECT c.`Intitule`, c.`Prix`, COUNT(*) AS nb FROM (SELECT Conso FROM `Historique` WHERE `Pg` = :pg ORDER BY Date DESC LIMIT 0,1000) AS h RIGHT JOIN Consos AS c ON h.`Conso` = c.`Id` WHERE c.`MotsClefs` LIKE :mot AND c.Actif = 1 GROUP BY c.`Intitule` ORDER BY nb DESC LIMIT 0 , 30");
        q.bindValue(":pg",id);
    }else{
        q.prepare("SELECT c.`Intitule`, c.`Prix`, COUNT(*) AS nb FROM (SELECT Conso FROM `Historique` ORDER BY Date DESC LIMIT 0,1000) AS h RIGHT JOIN Consos AS c ON h.`Conso` = c.`Id` WHERE c.`MotsClefs` LIKE :mot AND c.Actif = 1 GROUP BY c.`Intitule` ORDER BY nb DESC LIMIT 0 , 30");
    }
    q.bindValue(":mot","%"+recherche+"%");
    q.exec();
    QStringList l;
    while(q.next()){
        l.append(q.value(0).toString() + " : "+q.value(1).toString());
        //On ne conserve que les intitulés
    }
    return l;

}


int Statistiques::dernieresConsos(QList<int> *pgs, QList<QStringList *> *Consos, int limite){

    QSqlQuery q;
    q.prepare("SELECT h.Date, h.Pg, h.Conso, Consos.Intitule, COUNT(*) FROM Historique AS h INNER JOIN Consos ON h.Conso = Consos.Id GROUP BY h.Date DESC, h.Pg, h.Conso LIMIT 0,:lim");
    q.bindValue(":lim",10*limite);
    q.exec();

    int i = 0;
    if(q.first()){
        while(i < limite){
            int pg = q.value(1).toInt();
            QStringList *l = new QStringList();
            while(q.value(1).toInt() == pg){
                l->append(q.value(4).toString()+" x "+q.value(3).toString());
                if(!q.next()){
                    pgs->append(pg);
                    Consos->append(l);
                    return i+1;
                }
            }
            pgs->append(pg);
            Consos->append(l);
            i++;
        }
    }

    return i;
}

int Statistiques::grosConsommateurs(QList<int> *pgs, QList<double> *somme, TYPE type, int limite){

    QSqlQuery q;
    q.prepare("SELECT t.p, SUM(t.px) AS s FROM (SELECT h.Pg AS p, Consos.Prix AS px FROM Historique AS h INNER JOIN Consos ON h.Conso = Consos.Id) AS t GROUP BY t.p ORDER BY s DESC LIMIT 0,:lim");
    q.bindValue(":lim",limite);
    q.exec();

    int i = 0;
    while(q.next()){
        pgs->append(q.value(0).toInt());
        somme->append(q.value(1).toDouble());
        i++;
    }

    return i;
}
